package com.mbc.rmt.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import com.mbc.common.dao.DAO;
import com.mbc.rmt.data.BranchCashTransactionDataList;
import com.mbc.rmt.entities.BranchCashTransaction;
import com.mbc.rmt.entities.BranchCashTransactionRpt;

public class BranchCashTransactionDAOImpls extends DAO<BranchCashTransaction> implements BranchCashTransactionDAO {

	public BranchCashTransactionDAOImpls(Connection conn) {
		this.conn = conn;
		this.table_name = "branchcashtransaction";
	}

	public void create(BranchCashTransaction entity) throws SQLException {
		// TODO Auto-generated method stub
		query = "insert into "
				+ table_name
				+ " ( SlipID, SlipNo, SlipDate, ReceivedBranchID, DefaultBranchID, CreatedBy, ModifiedBy," +
				" CreatedDate, ModifiedDate, CurrencyID, Rate, ReceivedAmount, BankID, BankCode, TransactionType) values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		pstmt = conn.prepareStatement(query);
		int i = 1;
		pstmt.setLong(i++, entity.getSlipid());
		pstmt.setString(i++, entity.getSlipcode());		
		pstmt.setTimestamp(i++, new java.sql.Timestamp(entity.getSlipdate().getTime()));;
		pstmt.setLong(i++, entity.getBranchid());
		pstmt.setLong(i++, entity.getDefaultBranchID());
		pstmt.setLong(i++, entity.getCreateduser());
		pstmt.setLong(i++, entity.getModifieduser());
		pstmt.setTimestamp(i++, new java.sql.Timestamp(entity.getCreatedDate().getTime()));
		pstmt.setTimestamp(i++, new java.sql.Timestamp(entity.getModifiedDate().getTime()));
		pstmt.setLong(i++, entity.getCurrency_Id());
		pstmt.setDouble(i++, entity.getRate());
		pstmt.setDouble(i++, entity.getReceivedamt());
		if(entity.getBankid()!=null)
			pstmt.setDouble(i++, entity.getBankid());
		else
			pstmt.setString(i++, null);
		pstmt.setString(i++, entity.getBankcode());
		pstmt.setLong(i++, entity.getTransactiontype());
		
		pstmt.executeUpdate();
		pstmt.close();
	}

	public void update(BranchCashTransaction entity) throws SQLException {
		// TODO Auto-generated method stub
		query = "update "
				+ table_name
				+ " set SlipID = ?, SlipNo = ?, SlipDate = ?, ReceivedBranchID = ?, DefaultBranchID = ?, ModifiedBy = ?," +
				" CreatedDate = ?, ModifiedDate = ?, CurrencyID = ?, Rate = ?, ReceivedAmount = ?, BankID = ?, BankCode = ?, TransactionType = ?"
				+ " where SlipID = " + entity.getSlipid();
		pstmt = conn.prepareStatement(query);
		int i = 1;
		pstmt.setLong(i++, entity.getSlipid());
		pstmt.setString(i++, entity.getSlipcode());		
		pstmt.setTimestamp(i++, new Timestamp(entity.getSlipdate().getTime()));;
		pstmt.setLong(i++, entity.getBranchid());
		pstmt.setLong(i++, entity.getDefaultBranchID());	
		pstmt.setLong(i++, entity.getModifieduser());
		pstmt.setTimestamp(i++, new Timestamp(entity.getCreatedDate().getTime()));
		pstmt.setTimestamp(i++, new Timestamp(entity.getModifiedDate().getTime()));
		pstmt.setLong(i++, entity.getCurrency_Id());
		pstmt.setDouble(i++, entity.getRate());
		pstmt.setDouble(i++, entity.getReceivedamt());
		if(entity.getBankid()!=null)
			pstmt.setLong(i++, entity.getBankid());
		else
			pstmt.setString(i++, null);
		if(entity.getBankcode() !=null)
			pstmt.setString(i++, entity.getBankcode());
		else
			pstmt.setString(i++, null);
		pstmt.setLong(i++, entity.getTransactiontype());
		
		pstmt.executeUpdate();
		pstmt.close();
	}

	@Override
	public void delete(long key) throws SQLException {
		// TODO Auto-generated method stub
		query = getDeleteQuery() + " where invoice_Id = " + key;
		pstmt = conn.prepareStatement(query);
		pstmt.executeUpdate();
		pstmt.close();
	}
	
	@Override
	public void delete(BranchCashTransaction entity) throws SQLException {
		// TODO Auto-generated method stub
		query = "DELETE FROM "+table_name+" WHERE slipid = "+entity.getSlipid()+" AND transactiontype = "+entity.getTransactiontype();
		pstmt = conn.prepareStatement(query);
		pstmt.executeUpdate();
		pstmt.close();
	}
	@Override
	protected BranchCashTransaction readRecord(ResultSet rs)
			throws SQLException {
		// TODO Auto-generated method stub
		BranchCashTransaction l_entity = new BranchCashTransaction();
		l_entity.setSlipid(rs.getLong("slipid"));
		l_entity.setSlipcode(rs.getString("slipno"));
		l_entity.setSlipdate(rs.getTimestamp("slipdate"));
		l_entity.setBranchid(rs.getLong("receivedbranchid"));
		l_entity.setDefaultBranchID(rs.getLong("defaultbranchid"));
		l_entity.setCreateduser(rs.getLong("createdby"));
		l_entity.setModifieduser(rs.getLong("modifiedby"));
		l_entity.setCreatedDate(rs.getTimestamp("createddate"));
		l_entity.setModifiedDate(rs.getTimestamp("modifieddate"));
		l_entity.setCurrency_Id(rs.getLong("currencyid"));
		l_entity.setRate(rs.getDouble("rate"));
		l_entity.setReceivedamt(rs.getDouble("receivedamount"));
		l_entity.setBankid(rs.getLong("bankid"));
		l_entity.setBankcode(rs.getString("bankcode"));
		l_entity.setTransactiontype(rs.getByte("transactiontype"));
				
		return l_entity;		
	}

	@Override
	public BranchCashTransaction find(long key) throws SQLException {
		// TODO Auto-generated method stub
		BranchCashTransaction l_Branchcashtran = null;
		query = getSelectQuery() + " where slipid = " + key;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next())
			l_Branchcashtran = readRecord(rs);
		rs.close();
		pstmt.close();
		return l_Branchcashtran;
	}

	@Override
	public BranchCashTransactionDataList findBranchCashTranDataList(
			BranchCashTransaction cri) throws SQLException {
		// TODO Auto-generated method stub
		BranchCashTransactionDataList l_DataList = new BranchCashTransactionDataList();
		ArrayList<BranchCashTransaction> l_entityList = new ArrayList<BranchCashTransaction>();
		/*String l_cri = " and " + cri.getFieldname() + " like N'%" + cri.getKeyword() + "%'') " 
				+ " and defaultbranchid =" + cri.getBranchId() +" and transactiontype = "+cri.getStatus();*/
		
		String l_cri = "";
		
		if(!cri.getFieldname().equals("SlipDate"))
			l_cri += "and " + cri.getFieldname()  + " like N'%" + cri.getKeyword() + "%'";
		else
			l_cri += "and Convert(varchar(100), " + cri.getFieldname()  + ", 103) like N'%" + cri.getKeyword() + "%'";
		
		l_cri += " and defaultbranchid = " + cri.getBranchId() + " and transactiontype = " + cri.getStatus();
		
		query = "DECLARE @homecurrencyid bigint " +
				" SET @homecurrencyid=(SELECT HomeCurrencyid FROM Setup)" +
				"set rowcount "
				+ cri.getSize()
				+ " select * from (select DISTINCT row_number() over(order by slipno desc) as [rowno]," +
				" b.slipid, b.slipno, b.slipdate, b.receivedbranchid,br.branchcode, b.currencyid,ISNULL(cr.currencyrateid,0) currencyrateid, " +
				"  (CASE WHEN cr.currencyratecode IS NULL AND @homecurrencyid=b.currencyid" +
				" THEN c.currencycode+c.currencycode ELSE cr.currencyratecode END )currencyratecode," +
				" c.currencycode, b.rate, b.receivedamount, b.bankid, b.bankcode, b.transactiontype FROM " +
				 table_name + " b " + 
				" LEFT JOIN branch br ON br.branchid = b.receivedbranchid" +
				" INNER JOIN Currency c ON b.currencyid=c.currencyid" +
				" LEFT JOIN CurrencyRate cr ON cr.SourceCurrencyid = b.currencyid" +
				" AND cr.convertcurrencyid=@homecurrencyid and cr.branchid = " + cri.getBranchId() 				 
				+ " where 1 = 1 " + l_cri + ") " + table_name + " where rowno > " + cri.getIndex();
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next()) {
			do {
				l_entityList.add(readRecords(rs));
			} while (rs.next());
		}
		l_DataList.setEntityList(l_entityList);
		rs.close();
		pstmt.close();		
		String l_cri1 = " and (slipNo like N'%" + cri.getKeyword()
				+ "%')"  + "and defaultbranchid = " + cri.getBranchId()
				+ "and transactiontype = " + cri.getStatus();
		query = "select count(slipid) from " + table_name + " where 1 = 1"
				+ l_cri1;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next()) {
			l_DataList.setTotalRecord(rs.getLong(1));
		}
		rs.close();
		pstmt.close();
		return l_DataList;
	}
	
	@Override
	public BranchCashTransaction getBranchCashTransaction(
			BranchCashTransaction entity) throws SQLException {
		// TODO Auto-generated method stub
		BranchCashTransaction l_info = null;
	
		query = "DECLARE @homecurrencyid bigint " +
				" SET @homecurrencyid=(SELECT HomeCurrencyid FROM Setup)" +
				" select row_number() over(order by slipid) as [rowno],b.slipid, b.slipno, b.slipdate, " +
				" b.receivedbranchid,br.branchcode, b.currencyid," +
				"ISNULL(cr.currencyrateid,0) currencyrateid," +
				"  (CASE WHEN cr.currencyratecode IS NULL AND @homecurrencyid=b.currencyid" +
				" THEN c.currencycode+c.currencycode ELSE cr.currencyratecode END )currencyratecode," +
				"c.currencycode," +
				" b.rate, b.receivedamount, b.bankid, b.bankcode, b.transactiontype FROM branchcashtransaction b" +
				" LEFT JOIN branch br ON br.branchid = b.receivedbranchid" +
				" INNER JOIN Currency c ON b.currencyid=c.currencyid" +
				" LEFT JOIN CurrencyRate cr ON cr.SourceCurrencyid = b.currencyid" +
				" AND cr.convertcurrencyid=@homecurrencyid and  b.defaultbranchid  = " + entity.getDefaultBranchID() + 
				" where b.slipno = '" + entity.getSlipcode() + "' and b.transactiontype = " + entity.getTransactiontype() +
				" and b.defaultbranchid = " + entity.getDefaultBranchID(); 				
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next())
			l_info = readRecords(rs);
		rs.close();
		pstmt.close();
		return l_info;
	}

	protected BranchCashTransaction readRecords(ResultSet rs) throws SQLException {
		BranchCashTransaction l_entity = new BranchCashTransaction();
		l_entity.setSlipid(rs.getLong("slipid"));
		l_entity.setSlipcode(rs.getString("slipno"));
		l_entity.setSlipdate(rs.getTimestamp("slipdate"));
		l_entity.setBranchid(rs.getLong("receivedbranchid"));
		l_entity.setBranchcode(rs.getString("branchcode"));
		l_entity.setCurrency_Id(rs.getLong("currencyid"));
		l_entity.setCurrency_Code(rs.getString("currencycode"));
		l_entity.setCurrency_rate_Id(rs.getLong("currencyrateid"));
		l_entity.setCurrency_rate_Code(rs.getString("currencyratecode"));		
		l_entity.setRate(rs.getDouble("rate"));
		l_entity.setReceivedamt(rs.getDouble("receivedamount"));
		l_entity.setBankid(rs.getLong("bankid"));		
		l_entity.setBankcode(rs.getString("bankcode"));
		l_entity.setTransactiontype(rs.getByte("transactiontype"));
		return l_entity;
	}

	@Override
	public BranchCashTransaction findByCode(String code, long branchId)
			throws SQLException {
		// TODO Auto-generated method stub
		BranchCashTransaction l_info = null;
		query = "select  *  from BranchCashTransaction b  " + 
				" where b.SlipNo  = '" + code + "' and b.DefaultBranchID = " + branchId;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if(rs.next())
			l_info = readRecord(rs);
		rs.close();
		pstmt.close();
		return l_info;
	}
	//For Report
	@Override
	public ResultSet prepareBranchCashTransactionReport(
			BranchCashTransactionRpt info) throws SQLException {
		// TODO Auto-generated method stub
		ResultSet l_rs;
		String l_query = "dbo.sp_cr_BranchCashTransactionRpt ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?";

		CallableStatement l_pstmt = conn.prepareCall(l_query);
		BranchCashTransactionRptParameter(l_pstmt, info);
		l_rs = l_pstmt.executeQuery();
		return l_rs;
	}	
	
	private static void BranchCashTransactionRptParameter(CallableStatement l_pstmt,
			BranchCashTransactionRpt  info) throws SQLException {
		int i = 1;

		l_pstmt.setString(i++, (new java.sql.Timestamp(info.getFromdate().getTime())).toString());
		l_pstmt.setString(i++, (new java.sql.Timestamp(info.getTodate().getTime())).toString());
		l_pstmt.setString(i++, info.getFromSlipID());
		l_pstmt.setString(i++, info.getToSlipID());
		l_pstmt.setString(i++, info.getFromBranchID());
		l_pstmt.setString(i++, info.getToBranchID());
		l_pstmt.setString(i++, info.getFromUserID());
		l_pstmt.setString(i++, info.getToUserID());
		l_pstmt.setString(i++, info.getFromCurencyID());
		l_pstmt.setString(i++, info.getToCurrencyID());
		l_pstmt.setString(i++, info.getFromBankCheque());
		l_pstmt.setString(i++, info.getToBankCheque());
		l_pstmt.setString(i++, info.getGroupby());
		l_pstmt.setLong(i++, info.getBranchid());
		l_pstmt.setByte(i++, info.getTransactiontype());
	}
}
